/*
Navicat SQL Server Data Transfer

Source Server         : LOCALHOST
Source Server Version : 110000
Source Host           : .:1433
Source Database       : AAACHAM2
Source Schema         : dbo

Target Server Type    : SQL Server
Target Server Version : 110000
File Encoding         : 65001

Date: 2014-05-24 17:42:35
*/


-- ----------------------------
-- Table structure for tb0110
-- ----------------------------
DROP TABLE [dbo].[tb0110]
GO
CREATE TABLE [dbo].[tb0110] (
[TransactionID] varchar(20) NOT NULL ,
[VoucherID] varchar(20) NOT NULL ,
[InventoryID] varchar(20) NOT NULL ,
[UnitID] varchar(20) NULL ,
[ActualQuantity] money NULL ,
[UnitPrice] money NULL ,
[OriginalAmount] money NULL ,
[ConvertedAmount] money NULL ,
[Notes] nvarchar(250) NULL ,
[TranMonth] int NULL ,
[TranYear] int NULL ,
[DivisionID] varchar(20) NULL ,
[CurrencyID] varchar(20) NULL DEFAULT ('VND') ,
[ExchangeRate] money NULL DEFAULT ((1)) ,
[SaleUnitPrice] money NULL ,
[SaleAmount] money NULL ,
[DiscountAmount] money NULL ,
[SourceNo] varchar(20) NULL ,
[DebitAccountID] varchar(20) NULL ,
[CreditAccountID] varchar(20) NULL ,
[LocationID] varchar(20) NULL ,
[ImLocationID] varchar(50) NULL ,
[LimitDate] datetime NULL ,
[Orders] int NOT NULL DEFAULT ((0)) ,
[ConversionFactor] money NULL ,
[ReTransactionID] varchar(20) NULL ,
[ReVoucherID] varchar(20) NULL ,
[Ana01ID] varchar(20) NULL ,
[Ana02ID] varchar(20) NULL ,
[Ana03ID] varchar(20) NULL ,
[Ana04ID] varchar(20) NULL ,
[Ana05ID] varchar(20) NULL 
)


GO

-- ----------------------------
-- Indexes structure for table tb0110
-- ----------------------------

-- ----------------------------
-- Primary Key structure for table tb0110
-- ----------------------------
ALTER TABLE [dbo].[tb0110] ADD PRIMARY KEY NONCLUSTERED ([TransactionID])
GO

-- ----------------------------
-- Checks structure for table tb0110
-- ----------------------------
ALTER TABLE [dbo].[tb0110] ADD CHECK (([VoucherID] IS NOT NULL AND [VoucherID]<>''))
GO

-- ----------------------------
-- Triggers structure for table tb0110
-- ----------------------------
--DROP TRIGGER [dbo].[AX2017]
GO
CREATE TRIGGER [dbo].[AX2017]
ON [dbo].[tb0110]
AFTER DELETE
AS

Declare		@D07_Cursor		Cursor,
		@BaseCurrencyID as varchar(20)
Declare  	
	@KindVoucherID  	TinyInt ,
	@TransactionID		Varchar(20),
	@DivisionID		Varchar(20),		
	@RDVoucherID		Varchar(20), 	
	@InventoryID		Varchar(20), 	
	@MethodID		tinyint,
	@TranYear		Int,		
	@TranMonth		int,
	@ActualQuantity	Decimal(28,8),	
	@ConvertedQuantity	Decimal(28,8),	
	@ConvertedUnitPrice	Decimal(28,8),	
	@ConvertedAmount	Money,		
	@OriginalAmount	Money,
	@DebitAccountID	Varchar(20), 	
	@CreditAccountID	Varchar(20),
	@WareHouseID		Varchar(20),	
	@WareHouseID2	Varchar(20),
	@IsLimitDate 		tinyint,
	@IsSource		tinyint
	
Set @D07_Cursor = Cursor Scroll KeySet For
Select 		Del.TransactionID, Del.DivisionID, Del.VoucherID, Del.TranYear, Del.TranMonth,   ---- Thong tin chung				
		T09.WareHouseID, T09.WareHouseID2,    KindVoucherID,	
		Del.InventoryID, T02.MethodID as  MethodID,
		ActualQuantity, 
		ActualQuantity*isnull(T04.ConversionFactor,1),				
		OriginalAmount, ConvertedAmount,
		DebitAccountID,CreditAccountID,
		T02.IsLimitDate,
		T02.IsSource
From 	Deleted Del 	Inner Join tb0109 T09 	On 	Del.VoucherID = T09.VoucherID And 
							Del.DivisionID = T09.DivisionID And
							Del.TranMonth = T09.TranMonth And 
							Del.TranYear = T09.TranYear
			inner join tb0054 T02 	on 	Del.InventoryID = T02.InventoryID 
			Left  join tb0061 T04 	On	Del.UnitID 	= T04.UnitID and
							Del.InventoryID 	= T04.InventoryID

Open @D07_Cursor
Fetch Next From @D07_Cursor Into @TransactionID, @DivisionID, @RDVoucherID, @TranYear, @TranMonth,   ---- Thong tin chung		
		@WareHouseID, @WareHouseID2,   @KindVoucherID,		 	
		@InventoryID,   @MethodID, 	@ActualQuantity, 
		@ConvertedQuantity, @OriginalAmount, @ConvertedAmount,
		@DebitAccountID,@CreditAccountID,
		@IsLimitDate, @IsSource                                       

While @@FETCH_STATUS = 0
Begin	
		----- Xoa bang tinh gia	
		if @IsSource =1 Or @IsLimitDate =1 or @MethodID in (1,2,3)
				Delete tb0007 Where 	DivisionID =@DivisionID and
							WareHouseID =@WareHouseID and
							ReVoucherID =@RDVoucherID and
							ReTransactionID = @TransactionID

		
	Delete tb0191  Where TransactionID =@TransactionID and TableID ='tb0110'  and TransactionTypeID='T00'		
	Exec pr0065 @DivisionID, @WareHouseID, @TranMonth, @TranYear, @InventoryID, @ConvertedAmount, @ConvertedQuantity, @DebitAccountID, @CreditAccountID, 0 -- (1) la cap nhat tang,(0 ) la cap nhat giam

	Fetch Next From @D07_Cursor Into @TransactionID, @DivisionID, @RDVoucherID, @TranYear, @TranMonth,   ---- Thong tin chung		
			@WareHouseID, @WareHouseID2,   @KindVoucherID,		 	
			@InventoryID,   @MethodID, 	@ActualQuantity, 
			@ConvertedQuantity, @OriginalAmount, @ConvertedAmount,
			@DebitAccountID,@CreditAccountID,
			@IsLimitDate, @IsSource                                       
End
Close @D07_Cursor
DeAllocate @D07_Cursor



GO
--DROP TRIGGER [dbo].[AY2017]
GO
CREATE TRIGGER [dbo].[AY2017]
ON [dbo].[tb0110]
AFTER INSERT
AS
Declare		@D07_Cursor		Cursor,
		@BaseCurrencyID as varchar(20)

Declare  	
	@KindVoucherID  	TinyInt ,
	@TransactionID		Varchar(20),
	@DivisionID		Varchar(20),	@ModuleID		Varchar(20),
	@VoucherTypeID	Varchar(20),			
	@RDVoucherID		Varchar(20), 	@RDVoucherNo	Varchar(20),	
	@RDVoucherDate	Datetime,			
	@TranYear		Int,		@TranMonth		int,
	@InventoryID		Varchar(20),	@UnitID		Varchar(10),
	@ActualQuantity	Decimal(28,8),	@UnitPrice		Decimal(28,8),
	@ConvertedQuantity	Decimal(28,8),	@ConvertedUnitPrice	Decimal(28,8),
	@CurrencyID		Varchar(20),	@ExchangeRate	Money,		
	@ConvertedAmount	Money,		@OriginalAmount	Money,
	@DebitAccountID	Varchar(20), 	@CreditAccountID	Varchar(20),
	@WareHouseID		Varchar(20),	@WareHouseID2	Varchar(20),
	@Description		Varchar(255),	@SourceNo		Varchar(20),
	@CreateUserID		Varchar(20),	@CreateDate		Datetime,
	@EmployeeID		Varchar(20),	@MethodID		tinyint,
	@ObjectID		Varchar(20),
	@Status		tinyint,
	@IsLimitDate 		tinyint,
	@IsSource		tinyint,		
	@LimitDate		Datetime,
	@Orders as int ,
	@LastModifyUserID as varchar(20),
	@LastModifyDate as varchar(20),
	@Notes as varchar(250),
	@Ana01ID as varchar(20),	@Ana02ID as varchar(20),	@Ana03ID as varchar(20),
	@IsTemp as tinyint  --- Xy ly kho tam

Set @D07_Cursor = Cursor Scroll KeySet For
Select 		Ins.TransactionID, ins.DivisionID, ins.VoucherID, ins.TranYear, ins.TranMonth,   ---- Thong tin chung
		T09.VoucherDate,  T09.VoucherNo, 
		EmployeeID,  ObjectID,   T09.CreateUserID, T09.CreateDate,  Description,		
		T09.WareHouseID, T09.WareHouseID2,  0 , KindVoucherID,
		ins.CurrencyID, ins.ExchangeRate,
		ins.InventoryID, ins.UnitID,  T02.MethodID as MethodID, 
		ActualQuantity, UnitPrice,
		ActualQuantity*isnull(T04.ConversionFactor,1),		
		UnitPrice/isnull(T04.ConversionFactor,1),
		OriginalAmount, ConvertedAmount,
		DebitAccountID,CreditAccountID,    ins.SourceNo,
		T02.IsLimitDate, T02.IsSource, Ins.LimitDate,
		Ins.Orders , T09.LastModifyUserID, T09.LastModifyDate, Ins.Notes,
		Ins.Ana01ID, Ins.Ana02ID,Ins.Ana03ID,
		T03.IsTemp	

From 	inserted ins 	Inner Join tb0109 T09 	On 	ins.VoucherID = T09.VoucherID And 
							ins.DivisionID = T09.DivisionID And
							ins.TranMonth = T09.TranMonth And 
							ins.TranYear = T09.TranYear
			inner join tb0054 T02 	on 	ins.InventoryID = T02.InventoryID 
			Left  join tb0061 T04 	On	ins.UnitID 	= T04.UnitID and
							ins.InventoryID 	= T04.InventoryID
			Inner join tb0055 T03	on 	T03.WareHouseID = T09.WareHouseID and 
							T03.DivisionID = T09.DivisionID 




Open @D07_Cursor
Fetch Next From @D07_Cursor Into @TransactionID, @DivisionID, @RDVoucherID, @TranYear, @TranMonth,   ---- Thong tin chung
		@RDVoucherDate,  @RDVoucherNo, 
		@EmployeeID,  @ObjectID,   @CreateUserID, @CreateDate,  @Description,
		@WareHouseID, @WareHouseID2,  @Status, @KindVoucherID,
		@CurrencyID, @ExchangeRate,	
		@InventoryID, @UnitID,  @MethodID, 
		@ActualQuantity, @UnitPrice,
		@ConvertedQuantity, @ConvertedUnitPrice,	@OriginalAmount, @ConvertedAmount,
		@DebitAccountID,@CreditAccountID,    @SourceNo,
		@IsLimitDate, @IsSource, @LimitDate,@Orders ,@LastModifyUserID,@LastModifyDate,@Notes, @Ana01ID,@Ana02ID,@Ana03ID,
		@IsTemp	
                                         

While @@FETCH_STATUS = 0
Begin	

If (@IsSource<>0 or @IsLimitDate <>0 or @MethodID in (1,2,3) ) 			--- and    @KindVoucherID in (1,3)


	Begin
		---print 'Insert vao bang tinh gia xuat kho: tb0007'	
		Insert tb0007 (InventoryID, DivisionID, WareHouseID, ReVoucherID, ReTransactionID,   ReVoucherNo,
	                                	ReVoucherDate,  ReTranMonth,ReTranYear, ReSourceNo,    ReQuantity,  DeQuantity,
		                 	EndQuantity,    UnitPrice  ,  Status, LimitDate)
		Values (@InventoryID, @DivisionID, @WareHouseID, @RDVoucherID, @TransactionID,   @RDVoucherNo, 
			@RDVoucherDate,  @TranMonth, @TranYear, @SourceNo, @ConvertedQuantity, 0, 
			@ConvertedQuantity, 
			Case when @ConvertedQuantity<>0 then @ConvertedAmount/@ConvertedQuantity else @ConvertedUnitPrice End,
			0, @LimitDate)
	End



	Exec 	pr0066  @TransactionID, @DivisionID, @RDVoucherID, @TranYear, @TranMonth,   
		@RDVoucherDate,  @RDVoucherNo, 
		@EmployeeID,  @ObjectID,  @VoucherTypeID, @CreateUserID, @CreateDate,  @Description,
		@WareHouseID, @WareHouseID2,  @Status, @KindVoucherID,
		@CurrencyID, @ExchangeRate,	@InventoryID, @UnitID,  @MethodID, @ActualQuantity, @UnitPrice,
		@ConvertedQuantity,@ConvertedUnitPrice,	@OriginalAmount, @ConvertedAmount,
		@DebitAccountID,@CreditAccountID,    @SourceNo,
		@IsLimitDate, @IsSource, @LimitDate ,@Orders ,@LastModifyUserID,@LastModifyDate,@Notes,@Ana01ID,@Ana02ID,@Ana03ID,
		@IsTemp 

Fetch Next From @D07_Cursor Into @TransactionID, @DivisionID, @RDVoucherID, @TranYear, @TranMonth,   ---- Thong tin chung
		@RDVoucherDate,  @RDVoucherNo, 
		@EmployeeID,  @ObjectID,   @CreateUserID, @CreateDate,  @Description,
		@WareHouseID, @WareHouseID2,  @Status, @KindVoucherID,
		@CurrencyID, @ExchangeRate,	
		@InventoryID, @UnitID,  @MethodID, 
		@ActualQuantity, @UnitPrice,
		@ConvertedQuantity, @ConvertedUnitPrice,	@OriginalAmount, @ConvertedAmount,
		@DebitAccountID,@CreditAccountID,    @SourceNo,
		@IsLimitDate, @IsSource, @LimitDate , @Orders ,@LastModifyUserID,@LastModifyDate,@Notes,@Ana01ID,@Ana02ID,@Ana03ID,
		@IsTemp                                    

End
Close @D07_Cursor
DeAllocate @D07_Cursor








GO
--DROP TRIGGER [dbo].[AZ2017]
GO
CREATE TRIGGER [dbo].[AZ2017]
ON [dbo].[tb0110]
AFTER UPDATE
AS
Declare		@D07_Cursor		Cursor,
		@BaseCurrencyID as varchar(20)

Declare  	@TEST as varchar(800),
	@KindVoucherID  	TinyInt ,
	@TransactionID		Varchar(20),
	@DivisionID		Varchar(20),	@ModuleID		Varchar(20),
	@VoucherTypeID	Varchar(20),			
	@VoucherID		Varchar(20), 	@VoucherNo	Varchar(20),	
	@VoucherDate	Datetime,			
	@TranYear		Int,		@TranMonth		int,
	@InventoryID		Varchar(20),	@UnitID		Varchar(10),
	@ActualQuantity	Decimal(28,8),	@UnitPrice		Decimal(28,8),
	@ConvertedQuantity	Decimal(28,8),	@ConvertedUnitPrice	Decimal(28,8),
	@CurrencyID		Varchar(20),	@ExchangeRate	Money,		
	@ConvertedAmount	Money,		@OriginalAmount	Money,
	@DebitAccountID	Varchar(20), 	@CreditAccountID	Varchar(20),
	@WareHouseID		Varchar(20),	@WareHouseID2	Varchar(20),
	@Description		Varchar(255),	@SourceNo		Varchar(20),
	@CreateUserID		Varchar(20),	@CreateDate		Datetime,
	@EmployeeID		Varchar(20),	@MethodID		tinyint,
	@ObjectID		Varchar(20),
	@Status		tinyint,
	@IsLimitDate 		tinyint,
	@IsSource		tinyint,		
	@LimitDate		Datetime,
	@Orders as int ,
	@LastModifyUserID as varchar(20),
	@LastModifyDate as varchar(20),
	@Notes as varchar(250),		@Ana01ID as varchar(20),	@Ana02ID as varchar(20),	@Ana03ID as varchar(20),
	@IsTemp as Tinyint

Set @D07_Cursor = Cursor Scroll KeySet For
Select 		Del.TransactionID, Del.DivisionID, Del.VoucherID, Del.TranYear, Del.TranMonth,   ---- Thong tin chung				
		T09.WareHouseID, T09.WareHouseID2,    KindVoucherID,	
		Del.InventoryID, T02.MethodID as  MethodID,
		ActualQuantity, 
		ActualQuantity*isnull(T04.ConversionFactor,1),				
		OriginalAmount, ConvertedAmount,
		DebitAccountID,CreditAccountID,
		T02.IsLimitDate,
		T02.IsSource
From 	Deleted Del 	Inner Join tb0109 T09 	On 	Del.VoucherID = T09.VoucherID And 
							Del.DivisionID = T09.DivisionID And
							Del.TranMonth = T09.TranMonth And 
							Del.TranYear = T09.TranYear
			inner join tb0054 T02 	on 	Del.InventoryID = T02.InventoryID 
			Left  join tb0061 T04 	On	Del.UnitID 	= T04.UnitID and
							Del.InventoryID 	= T04.InventoryID

Open @D07_Cursor
Fetch Next From @D07_Cursor Into @TransactionID, @DivisionID, @VoucherID, @TranYear, @TranMonth,   ---- Thong tin chung		
		@WareHouseID, @WareHouseID2,   @KindVoucherID,		 	
		@InventoryID,   @MethodID, @ActualQuantity, 
		@ConvertedQuantity , @OriginalAmount, @ConvertedAmount,
		@DebitAccountID,@CreditAccountID,
		@IsLimitDate, @IsSource                                       

While @@FETCH_STATUS = 0
Begin	
		----- Xoa bang tinh gia	
		
			if @IsSource =1 Or @IsLimitDate =1 or @MethodID in (1,2,3)
			  Begin
				Update  tb0007 set 	
							ReQuantity = ReQuantity - @ActualQuantity,
							EndQuantity = EndQuantity - @ActualQuantity
				Where 	DivisionID =@DivisionID and
							WareHouseID =@WareHouseID and
							ReVoucherID =@VoucherID and
							ReTransactionID = @TransactionID
				
			End	

	----Print ' Van Nhan tes For Deleted '	+@TransactionID
	---@TEST =' Delete tb0191  Where '''+@TransactionID =@TransactionID and TableID ='tb0110' and TransactionTypeID='T00'		
	Delete tb0191  Where TransactionID =@TransactionID and TableID ='tb0110' and TransactionTypeID='T00'		
	Exec pr0065 @DivisionID, @WareHouseID, @TranMonth, @TranYear, @InventoryID, @ConvertedAmount, @ConvertedQuantity, @DebitAccountID, @CreditAccountID, 0 -- (1) la cap nhat tang,(0 ) la cap nhat giam

	Fetch Next From @D07_Cursor Into @TransactionID, @DivisionID, @VoucherID, @TranYear, @TranMonth,   ---- Thong tin chung		
			@WareHouseID, @WareHouseID2,   @KindVoucherID,		 	
			@InventoryID,   @MethodID, @ActualQuantity, 
			@ConvertedQuantity, @OriginalAmount, @ConvertedAmount,
			@DebitAccountID,@CreditAccountID,
			@IsLimitDate, @IsSource                                       
End
Close @D07_Cursor
DeAllocate @D07_Cursor




Set @D07_Cursor = Cursor Scroll KeySet For
Select 		Ins.TransactionID, ins.DivisionID, ins.VoucherID, ins.TranYear, ins.TranMonth,   ---- Thong tin chung
		T09.VoucherDate,  T09.VoucherNo, 
		EmployeeID,  ObjectID,   T09.CreateUserID, T09.CreateDate,  Description,		
		T09.WareHouseID, T09.WareHouseID2,  0 , KindVoucherID,
		ins.CurrencyID, ins.ExchangeRate,
		ins.InventoryID, ins.UnitID,  T02.MethodID as MethodID, 
		ActualQuantity, UnitPrice,
		ActualQuantity*isnull(T04.ConversionFactor,1),		
		UnitPrice/isnull(T04.ConversionFactor,1),
		OriginalAmount, ConvertedAmount,
		DebitAccountID,CreditAccountID,    ins.SourceNo,
		T02.IsLimitDate, T02.IsSource, Ins.LimitDate,
		Ins.Orders , T09.LastModifyUserID, T09.LastModifyDate, Ins.Notes, Ins.Ana01ID, Ins.Ana02ID, Ins.Ana03ID,
		T03.IsTemp	
	
		

From 	inserted ins 	Inner Join tb0109 T09 	On 	ins.VoucherID = T09.VoucherID And 
							ins.DivisionID = T09.DivisionID And
							ins.TranMonth = T09.TranMonth And 
							ins.TranYear = T09.TranYear
			inner join tb0054 T02 	on 	ins.InventoryID = T02.InventoryID 
			Left  join tb0061 T04 	On	ins.UnitID 	= T04.UnitID and
							ins.InventoryID 	= T04.InventoryID
			inner join tb0055  T03	 on	T03.WareHouseID = T09.WareHouseID and
							T03.DivisionID = T09.DivisionID
				
Open @D07_Cursor
Fetch Next From @D07_Cursor Into @TransactionID, @DivisionID, @VoucherID, @TranYear, @TranMonth,   ---- Thong tin chung
		@VoucherDate,  @VoucherNo, 
		@EmployeeID,  @ObjectID,   @CreateUserID, @CreateDate,  @Description,
		@WareHouseID, @WareHouseID2,  @Status, @KindVoucherID,
		@CurrencyID, @ExchangeRate,	
		@InventoryID, @UnitID,  @MethodID, 
		@ActualQuantity, @UnitPrice,
		@ConvertedQuantity, @ConvertedUnitPrice,	@OriginalAmount, @ConvertedAmount,
		@DebitAccountID,@CreditAccountID,    @SourceNo,
		@IsLimitDate, @IsSource, @LimitDate, @Orders , @LastModifyUserID, @LastModifyDate, @Notes, @Ana01ID, @Ana02ID, @Ana03ID,
		@IsTemp 
                                         

While @@FETCH_STATUS = 0
Begin	


		if @IsSource =1 Or @IsLimitDate =1 or @MethodID in (1,2,3)
			  Begin
				Update  tb0007 set 	UnitPrice = Case when ReQuantity + @ActualQuantity<>0 then @ConvertedAmount/ (ReQuantity + @ActualQuantity) else @UnitPrice End,
							ReQuantity = ReQuantity + @ActualQuantity,
							EndQuantity = EndQuantity +  @ActualQuantity
				Where 	DivisionID =@DivisionID and
							WareHouseID =@WareHouseID and
							ReVoucherID =@VoucherID and
							ReTransactionID = @TransactionID
				
			End	



	---Print 'Nhan TEST'
	Exec 	pr0066  @TransactionID, @DivisionID, @VoucherID, @TranYear, @TranMonth,   
		@VoucherDate,  @VoucherNo, 
		@EmployeeID,  @ObjectID,  @VoucherTypeID, @CreateUserID, @CreateDate,  @Description,
		@WareHouseID, @WareHouseID2,  @Status, @KindVoucherID,
		@CurrencyID, @ExchangeRate,	@InventoryID, @UnitID,  @MethodID, @ActualQuantity, @UnitPrice,
		@ConvertedQuantity,@ConvertedUnitPrice,	@OriginalAmount, @ConvertedAmount,
		@DebitAccountID,@CreditAccountID,    @SourceNo,
		@IsLimitDate, @IsSource, @LimitDate, @Orders , @LastModifyUserID, @LastModifyDate ,@Notes, @Ana01ID, @Ana02ID, @Ana03ID,
		@IsTemp                                       


Fetch Next From @D07_Cursor Into @TransactionID, @DivisionID, @VoucherID, @TranYear, @TranMonth,   ---- Thong tin chung
		@VoucherDate,  @VoucherNo, 
		@EmployeeID,  @ObjectID,   @CreateUserID, @CreateDate,  @Description,
		@WareHouseID, @WareHouseID2,  @Status, @KindVoucherID,
		@CurrencyID, @ExchangeRate,	
		@InventoryID, @UnitID,  @MethodID, 
		@ActualQuantity, @UnitPrice,
		@ConvertedQuantity, @ConvertedUnitPrice,	@OriginalAmount, @ConvertedAmount,
		@DebitAccountID,@CreditAccountID,    @SourceNo,
		@IsLimitDate, @IsSource, @LimitDate,@Orders , @LastModifyUserID, @LastModifyDate,@Notes, @Ana01ID, @Ana02ID, @Ana03ID,
		@IsTemp                                        

End
Close @D07_Cursor
DeAllocate @D07_Cursor









GO
